﻿using System;
using System.Collections.Generic;
using System.Text;
using MVCFoto.IDAL;
using MVCFoto.Model;
using System.Data.SqlClient;
using System.Data;

namespace MVCFoto.SQLServerDAL
{
    public class Photo:IPhoto
    {
        private const string PARM_ALBUMID = "@AlbumID";
        private const string PARM_USERID = "@UserID";
        private const string PARM_TITLE = "@Title";
        private const string PARM_DESC = "@Desc";
        private const string PARM_COVERPHOTO = "@CoverPhoto";
        private const string PARM_PHOTODATE = "@PhotoDate";
        private const string PARM_CREATEDATE = "@CreateDate";
        private const string PARM_PRIVACY = "@Privacy";
        private const string PARM_PHOTOCOUNT = "@PhotoCount";

        //photo columns
        private const string PARM_PHOTOID = "@PhotoID";
    


        private const string PARM_PHOTO = "@Photo";

        private const string PARM_VIEWS = "@Views";
        private const string PARM_EXIF_LNG = "@Exif_Lng";
        private const string PARM_EXIF_LAT = "@Exif_Lat";
        private const string PARM_EXIF_CAMERA = "@EXIF_Camera";
        private const string PARM_EXIF_MANUFACTURER = "@EXIF_Manufacturer";
        private const string PARM_EXIF_DATETIME = "@EXIF_DateTime";
        private const string PARM_EXIF_WIDTH = "@EXIF_Width";
        private const string PARM_EXIF_HEIGHT = "@EXIF_Height";
        private const string PARM_EXIF_EXPOSURETIME = "@EXIF_ExposureTime";
        private const string PARM_EXIF_FNUMBER = "@EXIF_FNumber";
        private const string PARM_EXIF_FOCALLENGTH = "@EXIF_FocalLength";
        private const string PARM_EXIF_FLASH = "@EXIF_Flash";
        private const string PARM_EXIF_ISO = "@EXIF_ISO";
        private const string PARM_EXIF_VERSION = "@EXIF_Version";
        private const string PARM_FILESIZE = "@FileSize";

        #region Album
        /// <summary>
        /// 取得用户的相册列表
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="privacy">允许查看的最高权限值</param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
     public IList<AlbumInfo> GetUserAlbums(int userId, short privacy,int page,int pagesize,out int itemCount)
     {         
         SqlParameter[] param = {
			            SqlHelper.MakeInParam(PARM_USERID,SqlDbType.Int,4,userId),                      
			          SqlHelper.MakeInParam(PARM_PRIVACY,SqlDbType.SmallInt,2,privacy),
                      SqlHelper.MakeInParam("@Page",SqlDbType.Int,4,page),	
			        SqlHelper.MakeInParam("@PageSize",SqlDbType.Int,4,pagesize),
                    SqlHelper.MakeOutParam("@ItemCount",SqlDbType.Int,4)
	            };
         IList<AlbumInfo> albums = null;
         using (SqlDataReader sdr = SqlHelper.ExecuteReader("mfp_Get_User_Albums", param))
         {
             albums = FetchAlbumFromReader(sdr);
             
         }
         itemCount = (int)param[4].Value;
         return albums;
     }
     public int CreateAlbum(AlbumInfo album)
     { 
            SqlParameter []param = {			          
			            SqlHelper.MakeInParam(PARM_USERID,SqlDbType.Int,4,album.UserID),	
			            SqlHelper.MakeInParam(PARM_TITLE,SqlDbType.VarChar,50,album.Title),	
			            SqlHelper.MakeInParam(PARM_DESC,SqlDbType.VarChar,1024,album.Desc),	
			            SqlHelper.MakeInParam(PARM_COVERPHOTO,SqlDbType.VarChar,255,album.CoverPhoto),	
			            SqlHelper.MakeInParam(PARM_PHOTODATE,SqlDbType.DateTime,0,album.PhotoDate),	
			            SqlHelper.MakeInParam(PARM_CREATEDATE,SqlDbType.DateTime,0,album.CreateDate),	
			            SqlHelper.MakeInParam(PARM_PRIVACY,SqlDbType.SmallInt,2,album.Privacy)
			           
	            };
         return (int)SqlHelper.ExecuteOneValue("mfp_Insert_Album",param);

     }
    public AlbumInfo GetAlbum(int albumId)
     { 
            SqlParameter []param = {
			            SqlHelper.MakeInParam(PARM_ALBUMID,SqlDbType.Int,4,albumId)	
			          
	            };
            AlbumInfo album = null;
            using (SqlDataReader sdr = SqlHelper.ExecuteReader("mfp_Get_Album", param))
            {
                IList<AlbumInfo> albums = FetchAlbumFromReader(sdr);
                if (albums != null && albums.Count > 0)
                    album = albums[0];                
            }
            return album;
     }
     public AlbumInfo GetAlbum(int userId, string title)
    {//
        SqlParameter[] param = {
			            SqlHelper.MakeInParam(PARM_USERID,SqlDbType.Int,4,userId),
                        SqlHelper.MakeInParam(PARM_TITLE,SqlDbType.VarChar,50,title)
			          
	            };
        AlbumInfo album = null;
        using (SqlDataReader sdr = SqlHelper.ExecuteReader("mfp_Get_Album_By_Title", param))
        {
            IList<AlbumInfo> albums = FetchAlbumFromReader(sdr);
            if (albums != null && albums.Count > 0)
                album = albums[0];
        }
        return album;
    }
        /// <summary>
        /// 更新相册的相片数
        /// </summary>
        /// <param name="albumId"></param>
        /// <param name="n"></param>
        /// <returns></returns>
     public bool UpdateAlbumPhotoCount(int albumId, int n)
     {
         SqlParameter[] param = {
			            SqlHelper.MakeInParam(PARM_ALBUMID,SqlDbType.Int,4,albumId),
                        SqlHelper.MakeInParam(PARM_PHOTOCOUNT,SqlDbType.Int,4,n)	
			          
	            };
         return SqlHelper.ExecuteNonQuery("mfp_Update_Album_PhotoCount", param) > 0 ? true : false;
     }

     public bool UpdateAlbum(AlbumInfo album)
     {
         //
         SqlParameter[] param = {			          
			            SqlHelper.MakeInParam(PARM_ALBUMID,SqlDbType.Int,4,album.AlbumID),	
			            SqlHelper.MakeInParam(PARM_TITLE,SqlDbType.VarChar,50,album.Title),	
			            SqlHelper.MakeInParam(PARM_DESC,SqlDbType.VarChar,1024,album.Desc),				        
			            SqlHelper.MakeInParam(PARM_PHOTODATE,SqlDbType.DateTime,0,album.PhotoDate),				         
			            SqlHelper.MakeInParam(PARM_PRIVACY,SqlDbType.SmallInt,2,album.Privacy)
			           
	            };
         return SqlHelper.ExecuteNonQuery("mfp_Update_Album", param) > 0 ? true : false;
     }

     IList<AlbumInfo> FetchAlbumFromReader(SqlDataReader sdr)
     {
         IList<AlbumInfo> albums = new List<AlbumInfo>();
         while (sdr.Read())
         {
             AlbumInfo album = new AlbumInfo();
             album.AlbumID = sdr.GetInt32(0);
             album.UserID = sdr.IsDBNull(1) ? 0 : sdr.GetInt32(1);
             album.Title = sdr.GetString(2);
             album.Desc = sdr.IsDBNull(3) ? "" : sdr.GetString(3);
             album.CoverPhoto = sdr.IsDBNull(4) ? "" : sdr.GetString(4);
             album.PhotoDate = sdr.IsDBNull(5) ? DateTime.Now : sdr.GetDateTime(5);
             album.CreateDate = sdr.IsDBNull(6) ? DateTime.Now : sdr.GetDateTime(6);
             album.Privacy = sdr.IsDBNull(7) ? (short)0 : sdr.GetInt16(7);
             album.PhotoCount = sdr.GetInt32(8);
             albums.Add(album);
         }
         return albums;

     }

     public bool UpdateAlbumCover(int albumId, string cover)
     { 
        SqlParameter[] param = {
			            SqlHelper.MakeInParam(PARM_ALBUMID,SqlDbType.Int,4,albumId),
                        SqlHelper.MakeInParam(PARM_COVERPHOTO,SqlDbType.VarChar,255,cover)	
			          
	            };
         return SqlHelper.ExecuteNonQuery("mfp_Update_Album_Cover",param) > 0?true:false;
     }
        #endregion

        #region Photo
     public int AddPhoto(PhotoInfo photo)
     { 
         SqlParameter[] param = {
		
			SqlHelper.MakeInParam(PARM_ALBUMID,SqlDbType.Int,4,photo.AlbumID),	
            SqlHelper.MakeInParam(PARM_USERID,SqlDbType.Int,4,photo.UserId),	
			SqlHelper.MakeInParam(PARM_TITLE,SqlDbType.VarChar,50,photo.Title),	
			SqlHelper.MakeInParam(PARM_DESC,SqlDbType.VarChar,1024,photo.Desc),	
			SqlHelper.MakeInParam(PARM_PHOTO,SqlDbType.VarChar,255,photo.Photo),	
			SqlHelper.MakeInParam(PARM_CREATEDATE,SqlDbType.DateTime,0,photo.CreateDate),		
			SqlHelper.MakeInParam(PARM_EXIF_LNG,SqlDbType.VarChar,10,photo.EXIF_Lng),	
			SqlHelper.MakeInParam(PARM_EXIF_LAT,SqlDbType.VarChar,50,photo.EXIF_Lat),	
			SqlHelper.MakeInParam(PARM_EXIF_CAMERA,SqlDbType.VarChar,50,photo.EXIF_Camera),	
			SqlHelper.MakeInParam(PARM_EXIF_MANUFACTURER,SqlDbType.VarChar,50,photo.EXIF_Manufacturer),	
			SqlHelper.MakeInParam(PARM_EXIF_DATETIME,SqlDbType.VarChar,50,photo.EXIF_DateTime),	
			SqlHelper.MakeInParam(PARM_EXIF_WIDTH,SqlDbType.VarChar,50,photo.EXIF_Width),	
			SqlHelper.MakeInParam(PARM_EXIF_HEIGHT,SqlDbType.VarChar,50,photo.EXIF_Height),	
			SqlHelper.MakeInParam(PARM_EXIF_EXPOSURETIME,SqlDbType.VarChar,50,photo.EXIF_ExposureTime),	
			SqlHelper.MakeInParam(PARM_EXIF_FNUMBER,SqlDbType.VarChar,50,photo.EXIF_FNumber),	
			SqlHelper.MakeInParam(PARM_EXIF_FOCALLENGTH,SqlDbType.VarChar,50,photo.EXIF_FocalLength),	
			SqlHelper.MakeInParam(PARM_EXIF_FLASH,SqlDbType.VarChar,50,photo.EXIF_Flash),	
			SqlHelper.MakeInParam(PARM_EXIF_ISO,SqlDbType.VarChar,50,photo.EXIF_ISO),	
			SqlHelper.MakeInParam(PARM_EXIF_VERSION,SqlDbType.VarChar,50,photo.EXIF_Version),
	        SqlHelper.MakeInParam(PARM_FILESIZE,SqlDbType.Int,4,photo.FileSize)
	
	        };
         return (int)SqlHelper.ExecuteOneValue("mfp_Insert_Photo", param);
     }

     public PhotoInfo GetPhoto(int photoId, bool updateViews)
     {
         
         SqlParameter[] param = {		
			SqlHelper.MakeInParam(PARM_PHOTOID,SqlDbType.Int,4,photoId),
            SqlHelper.MakeInParam(PARM_VIEWS,SqlDbType.Int,4,updateViews?1:0)
                                };
         PhotoInfo photo = null;
         using (SqlDataReader sdr = SqlHelper.ExecuteReader("mfp_Get_Photo", param))
         {
             IList<PhotoInfo> photos = FetchPhotosFromReader(sdr);
             if (photos.Count > 0)
                 photo = photos[0];
         }
         return photo;
     }
     public IList<PhotoInfo> GetUserNewPhotos(int userId, short privacy)
     {
         //
         SqlParameter[] param = {		
			SqlHelper.MakeInParam(PARM_USERID,SqlDbType.Int,4,userId),
            SqlHelper.MakeInParam(PARM_PRIVACY,SqlDbType.SmallInt,2,privacy)
                                };
         IList<PhotoInfo> photos = null;
         using (SqlDataReader sdr = SqlHelper.ExecuteReader("mfp_Get_User_Photos", param))
         {
             photos = new List<PhotoInfo>();
             while (sdr.Read())
             {
                 PhotoInfo photo = new PhotoInfo();
                 photo.PhotoID = sdr.GetInt32(0);
               
                 photo.Title = sdr.GetString(1);
                
                 photo.Photo = sdr.GetString(2);
                 photo.CreateDate = sdr.IsDBNull(3) ? DateTime.Now : sdr.GetDateTime(3);
                
                 photos.Add(photo);
             }
             
         }
         return photos;
     }

     public IList<PhotoInfo> GetAlbumPhotos(int albumId, int page, int pagesize)
     { 
        //
         SqlParameter[] param = {		
			SqlHelper.MakeInParam(PARM_ALBUMID,SqlDbType.Int,4,albumId),	
			SqlHelper.MakeInParam("@Page",SqlDbType.Int,4,page),	
			SqlHelper.MakeInParam("@PageSize",SqlDbType.Int,4,pagesize)
                                 };
         IList<PhotoInfo> photos;
         using (SqlDataReader sdr = SqlHelper.ExecuteReader("mfp_Get_Album_Photos", param))
         {
            photos = FetchPhotosFromReader(sdr);
         }
         return photos;
     }

     public bool UpdatePhoto(PhotoInfo photo)
     {
         SqlParameter[] param = {
		SqlHelper.MakeInParam(PARM_PHOTOID,SqlDbType.Int,4,photo.PhotoID),	
			//SqlHelper.MakeInParam(PARM_ALBUMID,SqlDbType.Int,4,photo.AlbumID),	
			SqlHelper.MakeInParam(PARM_TITLE,SqlDbType.VarChar,50,photo.Title),	
			SqlHelper.MakeInParam(PARM_DESC,SqlDbType.VarChar,1024,photo.Desc),	
			//SqlHelper.MakeInParam(PARM_PHOTO,SqlDbType.VarChar,255,photo.Photo),	
			
		//	SqlHelper.MakeInParam(PARM_EXIF_LNG,SqlDbType.VarChar,10,photo.EXIF_Lng),	
			//SqlHelper.MakeInParam(PARM_EXIF_LAT,SqlDbType.VarChar,50,photo.EXIF_Lat)	
			
	
	        };

         return SqlHelper.ExecuteNonQuery("mfp_Update_Photo", param) > 0 ?true:false;
     }
    /// <summary>
    /// 更新照片坐标
    /// </summary>
    /// <param name="photoId"></param>
    /// <param name="lat"></param>
    /// <param name="lng"></param>
    /// <returns></returns>
     public bool UpdatePhotoLatLng(int photoId, string lat, string lng)
     {
         SqlParameter[] param = {
		    SqlHelper.MakeInParam(PARM_PHOTOID,SqlDbType.Int,4,photoId),
			SqlHelper.MakeInParam(PARM_EXIF_LNG,SqlDbType.VarChar,10,lng),	
			SqlHelper.MakeInParam(PARM_EXIF_LAT,SqlDbType.VarChar,10,lat)		
                                };
         return SqlHelper.ExecuteNonQuery("mfp_Update_Photo_LatLng", param) > 0 ? true : false;
        
     }
        /// <summary>
        /// 删除照片
        /// </summary>
        /// <param name="photoId"></param>
        /// <returns></returns>
     public bool DeletePhoto(int photoId)
     {
         //
         SqlParameter[] param = {
		               SqlHelper.MakeInParam(PARM_PHOTOID,SqlDbType.Int,4,photoId)
                                };
         return SqlHelper.ExecuteNonQuery("mfp_Delete_Photo", param) > 0 ? true : false;
     }
    /// <summary>
    /// 根据Ids取得照片
    /// </summary>
    /// <param name="userId"></param>
     /// <param name="Ids">逗号分隔的照片Id列表</param>
    /// <returns></returns>
     public IList<PhotoInfo> GetPhotosByIds(int userId,string Ids)
     {
         SqlParameter[] param = { 
                                SqlHelper.MakeInParam(PARM_USERID,SqlDbType.Int,4,userId),
                                SqlHelper.MakeInParam("@Ids", SqlDbType.NVarChar,255,Ids)
                                };
         IList<PhotoInfo> photos;
         using (SqlDataReader sdr = SqlHelper.ExecuteReader("mfp_Get_Photos_By_Ids", param))
         {
             photos = FetchPhotosFromReader(sdr);
         }
         return photos;
     }
        /// <summary>
        /// 取得带指定tag的照片
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="tagName"></param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
     public IList<PhotoInfo> GetTagedPhotos(int userId, string tagName,short privacy, int page, int pagesize, out int itemcount)
     {
         SqlParameter[] param = {		
			SqlHelper.MakeInParam(PARM_USERID,SqlDbType.Int,4,userId),	
            SqlHelper.MakeInParam(PARM_PRIVACY,SqlDbType.SmallInt,2, privacy),	
            SqlHelper.MakeInParam("@TagName",SqlDbType.VarChar,50, tagName),	
			SqlHelper.MakeInParam("@Page",SqlDbType.Int,4,page),	
			SqlHelper.MakeInParam("@PageSize",SqlDbType.Int,4,pagesize),
            SqlHelper.MakeOutParam("@ItemCount",SqlDbType.Int,4)
                                 };
         IList<PhotoInfo> photos;
         using (SqlDataReader sdr = SqlHelper.ExecuteReader("mfp_Get_Taged_Photos", param))
         {
             photos = FetchPhotosFromReader(sdr);
         }
         itemcount = (int)param[5].Value;
         return photos;
     }

     IList<PhotoInfo> FetchPhotosFromReader(SqlDataReader sdr)
     {
         IList<PhotoInfo> photos = new List<PhotoInfo>();
         while (sdr.Read())
         {
             PhotoInfo photo = new PhotoInfo();
             photo.PhotoID = sdr.GetInt32(0);
             photo.AlbumID = sdr.IsDBNull(1) ? 0 : sdr.GetInt32(1);
             photo.UserId = sdr.IsDBNull(2) ? 0 : sdr.GetInt32(2);
             photo.Title = sdr.GetString(3);
             photo.Desc = sdr.IsDBNull(4) ? "" : sdr.GetString(4);
             photo.Photo = sdr.GetString(5);
             photo.CreateDate = sdr.IsDBNull(6) ? DateTime.Now : sdr.GetDateTime(6);
             photo.Views = sdr.IsDBNull(7) ? 0 : sdr.GetInt32(7);
             photo.EXIF_Lng = sdr.IsDBNull(8) ? "" : sdr.GetString(8);
             photo.EXIF_Lat = sdr.IsDBNull(9) ? "" : sdr.GetString(9);
             photo.EXIF_Camera = sdr.IsDBNull(10) ? "" : sdr.GetString(10);
             photo.EXIF_Manufacturer = sdr.IsDBNull(11) ? "" : sdr.GetString(11);
             photo.EXIF_DateTime = sdr.IsDBNull(12) ? "" : sdr.GetString(12);
             photo.EXIF_Width = sdr.IsDBNull(13) ? "" : sdr.GetString(13);
             photo.EXIF_Height = sdr.IsDBNull(14) ? "" : sdr.GetString(14);
             photo.EXIF_ExposureTime = sdr.IsDBNull(15) ? "" : sdr.GetString(15);
             photo.EXIF_FNumber = sdr.IsDBNull(16) ? "" : sdr.GetString(16);
             photo.EXIF_FocalLength = sdr.IsDBNull(17) ? "" : sdr.GetString(17);
             photo.EXIF_Flash = sdr.IsDBNull(18) ? "" : sdr.GetString(18);
             photo.EXIF_ISO = sdr.IsDBNull(19) ? "" : sdr.GetString(19);
             photo.EXIF_Version = sdr.IsDBNull(20) ? "" : sdr.GetString(20);
             photo.FileSize = sdr.IsDBNull(21) ? 0 : sdr.GetInt32(21);
             photos.Add(photo);
         }
         return photos;
     }
     #endregion

    }
}
